package com.rr.mymoney.upload.parser.excel;

import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.Locale;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.rr.mymoney.upload.ImportData;
import com.rr.mymoney.upload.Item;
import com.rr.mymoney.upload.parser.BankAccountTransactionsParser;

public class BcrCustomFormatParser implements BankAccountTransactionsParser {

    private static transient Logger log = Logger.getLogger(BcrCustomFormatParser.class);

    public SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy", new Locale("RO", "ro"));
    public NumberFormat numberFormat = NumberFormat.getInstance(new Locale("RO", "ro"));

    public boolean test(Workbook workbook) {
	//
	// Get the first sheet on the workbook.
	//
	try {
	    Sheet sheet = workbook.getSheetAt(0);

	    //
	    // When we have a sheet object in hand we can iterator on
	    // each sheet's rows and on each row's cells. We store the
	    // data read on an ArrayList so that we can printed the
	    // content of the excel to the console.
	    //

	    Row firstRow = sheet.getRow(0);
	    Row secondRow = sheet.getRow(1);

	    if (!firstRow.getCell(0).getStringCellValue().contains("Total tranzactii de creditare")
		    || !firstRow.getCell(2).getStringCellValue().contains("Total tranzactii de debitare")
		    || !secondRow.getCell(0).getStringCellValue().contains("Data procesarii")
		    || !secondRow.getCell(1).getStringCellValue().contains("Canal de tranzactionare")
		    || !secondRow.getCell(2).getStringCellValue().contains("Detalii operatiune")
		    || !secondRow.getCell(3).getStringCellValue().contains("Suma")) {
		return false;
	    }
	} catch (Exception e) {
	    log.debug("Error in test:", e);
	    return false;
	}
	return true;
    }

    public ImportData parse(Workbook workbook) throws ParseException {

	ImportData importFile = new ImportData();
	// Get the first sheet on the workbook.
	Sheet sheet = workbook.getSheetAt(0);

	// When we have a sheet object in hand we can iterator on each sheet's
	// rows and on each row's cells. We store the data read on an ArrayList
	// so that we can printed the content of the excel to the console.

	numberFormat.setGroupingUsed(true);
	Row row = sheet.getRow(0);
	try {
	    String incomeString = row.getCell(1).getStringCellValue().trim();
	    importFile.setTotalIncome(numberFormat.parse(incomeString).floatValue());
	} catch (Exception e) {
	    importFile.setTotalIncome((float) row.getCell(1).getNumericCellValue());
	}

	try {
	    String spendingsString = row.getCell(3).getStringCellValue().trim();
	    importFile.setTotalExpense(numberFormat.parse(spendingsString).floatValue());
	} catch (Exception e) {
	    importFile.setTotalExpense((float) row.getCell(3).getNumericCellValue());
	}

	Item item = new Item();
	Iterator<Row> rowsIterator = sheet.rowIterator();
	while (rowsIterator.hasNext()) {
	    row = rowsIterator.next();

	    Cell cell = row.getCell(0);
	    if (cell != null) {
		String value = cell.getStringCellValue();
		if (value != null && value.trim().length() > 0) {
		    value = value.trim();

		    if (value.contains("Total tranzactii de creditare") || value.contains("Data procesarii")) {

			// do nothing - go to next line

		    } else {
			Date date = dateFormat.parse(value);

			// if the date is ok create a new item
			item = new Item();
			importFile.getItems().add(item);

			Calendar calendar = Calendar.getInstance();
			calendar.setTime(date);
			calendar.set(Calendar.HOUR_OF_DAY, 12);
			item.setDate(calendar.getTime());

			// set details
			cell = row.getCell(1);
			String details = cell.getStringCellValue();
			if (details != null) {
			    item.setDetails((item.getDetails() != null ? item.getDetails() : "") + "Canal de tranzactionare: " + details
				    + " ");
			}

			cell = row.getCell(2);
			details = cell.getStringCellValue();
			if (details != null) {
			    item.setDetails((item.getDetails() != null ? item.getDetails() : "") + details + " ");
			}

			// set value
			cell = row.getCell(3);
			try {
			    String debit = cell.getStringCellValue();
			    if (debit != null && debit.trim().length() > 0) {
				Number debitNumber = numberFormat.parse(debit);
				item.setValue(debitNumber.floatValue());
			    }
			} catch (Exception e) {
			    item.setValue((float) cell.getNumericCellValue());
			}
		    }
		}
	    }
	}
	return importFile;
    }

}
